package com.qf.studentmanager.dao;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.qf.studentmanager.config.DBConfig;
import com.qf.studentmanager.model.Clazz;
import com.qf.studentmanager.model.Course;
import com.qf.studentmanager.model.Grade;
import com.qf.studentmanager.model.Teacher;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TeacherDao {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);

    public Long getTotal() {
        try {
            return  queryRunner.query("select count(1) from teacher", new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Teacher> getTeahcerByPage(int i, int r, String sort, String order) {
//        String sql2 = "select t.* , c.* from teacher t    left join teacher_course tc on t.tid = tc.tid    left join  course c on c.cid = tc.courseId    left join clazz cl on c.cid = c.cid    left join grade g on cl.gid = g.gid order by" + sort + " " + order + " limit ?,?";

        try {
            String sql = "select t.*,c.*,cl.`cid` as clid,cl.`clazzName` as clazzName,g.* from teacher t left join teacher_course tc on t.`tid`=tc.`tid` left join course c on tc.`courseId`=c.`cid` left join clazz cl on tc.`clazzId`=cl.`cid` left join grade g on tc.`gid`=g.`gid` order by " + sort + " " + order + " limit ?,?";
            return   queryRunner.query(sql, new ResultSetHandler<List<Teacher>>() {
                  @Override
                  public List<Teacher> handle(ResultSet resultSet) throws SQLException {
                      List<Teacher> teachers = new ArrayList<>();
                      Teacher teacher = null;
                      int  lastTid = -1;
                      while (resultSet.next()){
                          int tid = resultSet.getInt("tid");
                          if (tid == lastTid ){
                              //教师已添加，只需修改教师的list属性
                              int cid = resultSet.getInt("cid");
                              String courseName = resultSet.getString("courseName");

                              int clid = resultSet.getInt("clid");
                              String clazzName = resultSet.getString("clazzName");

                              int gid = resultSet.getInt("gid");
                              String gradeName = resultSet.getString("gradeName");


                              Course course = new Course();
                              course.setCid(cid);
                              course.setCourseName(courseName);

                              Clazz clazz = new Clazz();
                              clazz.setCid(clid);
                              clazz.setClazzName(clazzName);

                              Grade grade = new Grade();
                              grade.setGid(gid);
                              grade.setGradeName(gradeName);

                              Map<String , Object> map = new HashMap<>();
                              map.put("grade", grade);
                              map.put("clazz", clazz);
                              map.put("course", course);

                              teacher.getCourses().add(map);


                          }else {
                              teacher = new Teacher();
                              //发现新教师，所有信息都要从rs中拿出
                              teacher.setTid(tid);
                              String number = resultSet.getString("number");
                              teacher.setNumber(number);
                              String name = resultSet.getString("name");
                              teacher.setName(name);
                              String gender = resultSet.getString("gender");
                              teacher.setGender(gender);
                              String phone = resultSet.getString("phone");
                              teacher.setPhone(phone);
                              String qq = resultSet.getString("qq");
                              teacher.setQq(qq);

                              int cid = resultSet.getInt("cid");
                              String courseName = resultSet.getString("courseName");

                              int clid = resultSet.getInt("clid");
                              String clazzName = resultSet.getString("clazzName");

                              int gid = resultSet.getInt("gid");
                              String gradeName = resultSet.getString("gradeName");


                              Course course = new Course();
                              course.setCid(cid);
                              course.setCourseName(courseName);

                              Clazz clazz = new Clazz();
                              clazz.setCid(clid);
                              clazz.setClazzName(clazzName);

                              Grade grade = new Grade();
                              grade.setGid(gid);
                              grade.setGradeName(gradeName);

                              Map<String , Object> map = new HashMap<>();
                              map.put("grade", grade);
                              map.put("clazz", clazz);
                              map.put("course", course);

                              List<Map<String , Object>> courses = new ArrayList<>();
                              courses.add(map);
                              teacher.setCourses(courses);

                              teachers.add(teacher);
                          }
                          lastTid = tid;
                      }

                      return teachers;
                  }
              },i , r);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Teacher getTeacherByPhone(String number) {
        try {
            return queryRunner.query("select * from teacher where number = ?;",new BeanHandler<>(Teacher.class),number);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Integer addTeacher(Teacher teacher) throws SQLException {
        Connection connection = DBConfig.getConnection();
        String sql = "insert into  teacher ( number, name, gender, phone, qq) VALUES (?,?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, teacher.getNumber());
        preparedStatement.setString(2, teacher.getName());
        preparedStatement.setString(3, teacher.getGender());
        preparedStatement.setString(4, teacher.getPhone());
        preparedStatement.setString(5, teacher.getQq());
        int i = preparedStatement.executeUpdate();
        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        if (resultSet.next()){
            int anInt = resultSet.getInt(1);
            teacher.setTid(anInt);
        }
        return i ;
    }

    public int addTeacherAndCourse(Teacher teacher ,String[] courses) throws SQLException {
        Connection connection = DBConfig.getConnection();
        int i = 0;
        for (String str :courses) {
            String[] s = str.split("_");
            String gid = s[0];
            String clid = s[1];
            String cid = s[2];
            Integer tid = teacher.getTid();
            queryRunner.update(connection, "insert into teacher_course (tid, gid, clazzId, courseId) VALUES (?,?,?,?)",tid,gid,clid,cid);
            i++;
        }
        return i;
    }

    public Integer updateTeacher(Teacher teacher) throws SQLException {
        Connection connection = DBConfig.getConnection();
        return queryRunner.update(connection, "update teacher set  name=? , gender=? , phone=? , qq=? where number = ?;", teacher.getName() , teacher.getGender() , teacher.getPhone() , teacher.getQq() , teacher.getNumber());

    }

    public Integer deleteTeacherCourse(Integer tid) throws SQLException {
        Connection connection = DBConfig.getConnection();
        return queryRunner.update(connection, "delete from teacher_course where  tid = ?",tid);
    }

    //根据tid是这个数组批量删除老师信息。
    public Integer deleteTeahcers(String[] tids) throws SQLException {
        Connection connection = DBConfig.getConnection();
        StringBuffer sql = new StringBuffer("delete from teacher where tid in (");
        for (int i = 0; i < tids.length; i++) {
            if (i == tids.length-1){
                sql.append("?)");
            }else {
                sql.append("?,");
            }
        }

        return queryRunner.update(connection, sql.toString(),tids);
    }

    //根据tid，批量删除老师与课程的关联表。
    public Integer delTeacherAndCourses(String[] tids) throws SQLException {
        Connection connection = DBConfig.getConnection();
        StringBuffer sql = new StringBuffer("delete from teacher_course where tid in (");
        for (int i = 0; i < tids.length; i++) {
            if (i == tids.length-1){
                sql.append("?)");
            }else {
                sql.append("?,");
            }
        }
        return queryRunner.update(connection, sql.toString(),tids);
    }
}
